package tp1.dao;
import com.mysql.jdbc.Statement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import tp1.DbConnection;
import tp1.models.Plano;

/**
 * Implementação DAO usando JDBC
 * @author tulio
 */
public class PlanoDao {

    public static int delete(String codigo) throws SQLException {
        Statement stmt = (Statement) DbConnection.getInstance().createStatement();
        return stmt.executeUpdate("DELETE FROM PLANO WHERE CODIGO = '" + codigo + "'");
    }

    public static Plano find(int codigo) throws SQLException {
        Plano p = null;
        Statement stmt = (Statement) DbConnection.getInstance().createStatement();
        ResultSet rs = stmt.executeQuery("SELECT * FROM PLANO WHERE CODIGO = '" + codigo + "'");

        if(rs.next()){
            p = getPlanoByResultSet(rs, null);
        }
        return p;        
    }

    public static ArrayList<Plano> list() throws SQLException {
        Plano p;
        ArrayList<Plano> list = new ArrayList();
        Statement stmt = (Statement) DbConnection.getInstance().createStatement();
        ResultSet rs = stmt.executeQuery("SELECT * FROM PLANO");

        while(rs.next()){
            list.add(getPlanoByResultSet(rs, null));
        }
        
        return list;
    }

    public static int insert(Plano plano) throws SQLException {
        String insert = "INSERT INTO PLANO (DESCRICAO, FRANQUIA, VALOR_DA_CHAMADA, VALOR_MENSAL) VALUES('" + 
                    plano.getDescricao() + "'," +
                    plano.getFranquia() + "," +
                    plano.getValorDaChamada() + "," +
                    plano.getValorMensal() + ")";
        
        Statement stmt = (Statement) DbConnection.getInstance().createStatement();
        return stmt.executeUpdate(insert);
    }

    public static int update(Plano plano) throws SQLException {
        String update = "UPDATE PLANO SET DESCRICAO = '" + plano.getDescricao() +
                "', FRANQUIA = " + plano.getFranquia() +
                ", VALOR_DA_CHAMADA = " + plano.getValorDaChamada() +
                ", VALOR_MENSAL = " + plano.getValorMensal() +
                " WHERE CODIGO = " + plano.getCodigo();
        Statement stmt = (Statement) DbConnection.getInstance().createStatement();
        return stmt.executeUpdate(update);
    }

    public static int save(Plano plano) throws SQLException{
        int result;
        if(find(plano.getCodigo()) == null){
            result = insert(plano);
        } else {
            result = update(plano);
        }
        return result;
    }

    public static Plano getPlanoByResultSet(ResultSet rs, String alias) throws SQLException{
        if(alias == null) alias = "PLANO";
        Plano p = new Plano(rs.getInt(alias + ".CODIGO"));
        p.setDescricao(rs.getString(alias + ".DESCRICAO"));
        p.setFranquia(rs.getInt(alias + ".FRANQUIA"));
        p.setValorDaChamada(rs.getFloat(alias + ".VALOR_DA_CHAMADA"));
        p.setValorMensal(rs.getFloat(alias + ".VALOR_MENSAL"));
        return p;
    }

    public static Object[][] listAsVector() throws SQLException{
        int row = 0;
        ArrayList<Plano> planos = PlanoDao.list();
        Object[][] rows;

        if(planos.isEmpty()){
            rows = null;
        } else {
            rows = new Object[planos.size()][5];
        }

        for (Plano p : planos) {
            rows[row][0] = p.getCodigo();
            rows[row][1] = p.getDescricao();
            rows[row][2] = p.getValorDaChamada();
            rows[row][3] = p.getValorMensal();
            rows[row][4] = p.getFranquia();
            row++;
        }
        return rows;
    }

    public static Object[][] findAsVector(int codigo) throws SQLException{
        Plano plano = PlanoDao.find(codigo);
        Object[][] rows = null;
        if(plano != null){
            rows = new Object[1][5];
            rows[0][0] = plano.getCodigo();
            rows[0][1] = plano.getDescricao();
            rows[0][2] = plano.getValorDaChamada();
            rows[0][3] = plano.getValorMensal();
            rows[0][4] = plano.getFranquia();
        }
        return rows;
    }

}
